﻿// ------------------------------------------------------------------------------------
//      Copyright (c) uhavemyword@gmail.com. All rights reserved.
//      Created by Ben at 1/17/2014 1:48:00 PM
// ------------------------------------------------------------------------------------

namespace CP.General
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;

    public class DbHelper
    {
        private DbConnection _connection;

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connection">Can be SqlConnection, OleDbConnection, OdbcConnection...</param>
        public DbHelper(DbConnection connection)
        {
            this._connection = connection;
        }

        public DbConnection Connection
        {
            get
            {
                return _connection;
            }
            set
            {
                _connection = value;
            }
        }

        public int ExecuteNonQuery(string command, CommandType commandType = CommandType.Text, DbParameter[] parameters = null)
        {
            int affected;
            DbConnection conn = _connection;
            DbTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = command;
                    cmd.CommandType = commandType;
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    affected = cmd.ExecuteNonQuery();
                }
                trans.Commit();
                return affected;
            }
            catch
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
                throw;
            }
            finally
            {
                if (trans != null)
                {
                    trans.Dispose();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        public int ExecuteNonQuery(List<string> commandTexts)
        {
            int affected = -1;
            DbConnection conn = _connection;
            DbTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.Transaction = trans;
                    cmd.CommandType = CommandType.Text;
                    foreach (string command in commandTexts)
                    {
                        cmd.CommandText = command;
                        affected += cmd.ExecuteNonQuery();
                    }
                }
                trans.Commit();
                return affected;
            }
            catch
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
                throw;
            }
            finally
            {
                if (trans != null)
                {
                    trans.Dispose();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        ///    Executes the query and returns the first column of the first row in the result
        ///    set returned by the query. All other columns and rows are ignored.
        /// </summary>
        /// <param name="command">command text</param>
        /// <returns>The first column of the first row in the result set.</returns>
        public object ExecuteScalar(string command, CommandType commandType = CommandType.Text, DbParameter[] parameters = null)
        {
            DbConnection conn = _connection;
            try
            {
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = command;
                    cmd.CommandType = commandType;
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    return cmd.ExecuteScalar();
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        public DataTable ExecuteQuery(string command, CommandType commandType = CommandType.Text, DbParameter[] parameters = null)
        {
            DataTable dtReturn = new DataTable();
            DbConnection conn = _connection;
            try
            {
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = command;
                    cmd.CommandType = commandType;
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        dtReturn.Load(reader);
                    }
                }
                return dtReturn;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
    }
}
